# -*- coding: utf-8 -*-
# @Time:2021/07/11 14:36
# @Author:Cindy

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# 设置flask关联的数据库
username = 'root'
pwd = '123456'
ip = '192.168.1.108'
port = '3306'
database = 'students'
app.config['SQLALCHEMY_DATABASE_URI'] = \
    f'mysql+pymysql://{username}:{pwd}@{ip}:{port}/{database}?charset=utf8'

# app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///test.db'
db = SQLAlchemy(app)


class User(db.Model):
    # 设定表名
    __tablename__ = "client"
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    gender = db.Column(db.String(10))

    # 魔法方法，打印的时候调用
    def __repr__(self):
        return '<User %r>' % self.username


if __name__ == '__main__':
    # # 删除表
    db.drop_all()
    # 创建表
    db.create_all()
    user1 = User(id=1, username='Lisa', email='Lisa@126.com', gender='女')
    user2 = User(id=2, username='Liya', email='Liya@126.com', gender='女')
    user3 = User(id=3, username='Tom', email='Tom@126.com', gender='男')

    db.session.add(user1)
    db.session.add(user2)
    db.session.add(user3)
    db.session.commit()

    # 查询所有内容
    res = User.query.all()
    print(res)

    # # 条件查找
    # res = User.query.filter_by(id=3).first()
    # print(res.username, res.email, res.gender)
    # # 通过表达式进行条件查询
    # res = User.query.filter(User.id > 2).all()
    # print(res)
    # # 删除操作
    # User.query.filter_by(id=3).delete()
    # db.session.commit()
    # # 修改操作1，直接修改属性
    # res = User.query.filter_by(id=2).first()
    # res.email = 'Liya01@126.com'
    # db.session.commit()
    # 修改操作2，
    res = User.query.filter_by(id=2).update({'gender': '男', 'email': 'Liya01@126.com'})
    db.session.commit()



